# 添加视频内容去重字段和视频内容的宽高、帧率、总帧数、时长，，判断图片是否还存在然后更新download_status
import cv2
import hashlib
import os
import pymysql

MySQL_PARAMS = {
    "host": "192.168.1.45",
    "user": "xzl",
    "password": "Xzl4kyk!",
    "port": 3306,
    "database": "qcc_company_graph",
    "charset": "utf8mb4",
}
PRE_PATH = r'F:\video\mixkit'
mysql_conn = pymysql.connect(**MySQL_PARAMS)
mysql_course = mysql_conn.cursor()


def get_video_info(video_path):
    # 打开视频文件
    video_capture = cv2.VideoCapture(video_path)

    # 获取视频的帧宽度、帧高度和帧率
    video_width = int(video_capture.get(cv2.CAP_PROP_FRAME_WIDTH))
    video_height = int(video_capture.get(cv2.CAP_PROP_FRAME_HEIGHT))
    frame_rate = video_capture.get(cv2.CAP_PROP_FPS)

    # 获取视频的总帧数和时长（秒）
    total_frames = int(video_capture.get(cv2.CAP_PROP_FRAME_COUNT))
    video_time = round(total_frames / frame_rate, 2) if frame_rate > 0 else 0


    # 关闭视频文件
    video_capture.release()
    return video_width, video_height, video_time, frame_rate, total_frames


def update_video_detail_data(id, video_width, video_height, video_time, frame_rate, total_frames):
    # 更新mysql的md5值
    update_video_detail_data_sql = "UPDATE video_info SET video_width = '%s', video_height = '%s', video_time = '%s', frame_rate = '%s',total_frames = '%s', update_on = NOW() WHERE id = '%s'" % (video_width, video_height, video_time, frame_rate, total_frames, id)
    mysql_course.execute(update_video_detail_data_sql)
    mysql_conn.commit()

def update_image_download_status(id):
    # 更新mysql的md5值
    update_image_md5_sql = "UPDATE video_info SET download_status = 0,update_on = NOW() WHERE id = '%s'" % (id,)
    mysql_course.execute(update_image_md5_sql)
    mysql_conn.commit()

# 根据计算图片二进制流的md5值，判断图片是否重复，并判断图片是否存在然后更新download_status
def calculate_md5(file_path, id):
    # 以二进制模式打开文件
    try:
        with open(file_path, 'rb') as file:
            # 读取文件内容
            file_content = file.read()
            # 计算 MD5 哈希值
            md5_hash = hashlib.md5(file_content).hexdigest()
    except Exception as e:
        print('找不到文件')
        update_image_download_status(id)
        return None
    return md5_hash


def update_video_md5(id, md5_hash):
    # 更新mysql的md5值
    update_video_md5_sql = "UPDATE video_info SET video_md5 = '%s',update_on = NOW() WHERE id = '%s'" % (md5_hash, id)
    mysql_course.execute(update_video_md5_sql)
    mysql_conn.commit()


def get_mysql_video():
    select_video_sql = "SELECT id, local_path FROM video_info "
    mysql_course.execute(select_video_sql)
    result = mysql_course.fetchall()
    return result


def run():
    video_data = get_mysql_video()
    for video in video_data:
        video_id = video[0]
        file_path = video[1]
        if not file_path:
            continue
        video_path = os.path.join(PRE_PATH, file_path)
        video_md5 = calculate_md5(video_path, video_id)
        if not video_md5:
            continue
        update_video_md5(video_id, video_md5)
        video_width, video_height, video_time, frame_rate, total_frames = get_video_info(video_path)
        update_video_detail_data(video_id, video_width, video_height, video_time, frame_rate, total_frames)
        print('video_id: %s, 更新成功' % video_id)



# 对文件夹中的图片去重
run()

